package com.huaxindata.im.model.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import com.huaxindata.im.model.entity.ChatMessage;
import com.huaxindata.im.model.entity.ChatMessageBody;
import com.huaxindata.im.model.entity.ChatSysMessageBody;
import com.huaxindata.im.model.utils.ReceivePlayload;
import com.huaxindata.im.model.utils.SendPlayload;
import com.huaxindata.im.util.Log;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by Administrator on 2017/8/15.
 * 一个管理消息的工具类，主要是对数据库的增删改查等操作
 * 需要注意的是，在Application中要进行初始化，不然会抛出空指针异常
 */

public class MessageStore {

    private static final String TAG = "MessageStore";
    private static MQTTDatabaseHelper sDatabaseHelper;
    private static SQLiteDatabase     sDatabase;

    /**
     * 初始化数据库
     */
    public static void init(Context context) {
        if (sDatabaseHelper == null) {
            sDatabaseHelper = new MQTTDatabaseHelper(context);
            sDatabase = sDatabaseHelper.getWritableDatabase();
        }
    }

    /**
     * 向数据库中插入一条消息对象
     * 返回true表示插入成功，返回false表示插入失败
     */
    public static boolean insert(ChatMessage message) {
        if (message == null) {
            return false;
        }
        //如果是系统消息，则只存储添加好友请求和被添加好友请求 同意添加回复 同意被添加请求 创建群 删除群
        if (message.type == ChatMessageBody.Type.SYSTEM) {
            ChatSysMessageBody chatSysMessageBody = (ChatSysMessageBody) message.getMessageBody();
            String action = chatSysMessageBody.getAction();

            //非同意添加回复
            if (ChatSysMessageBody.SysMessageType.ACTION_RESP_FED.equals(action) && !"1".equals(chatSysMessageBody.getSystemMessageInfo()[2])) {
                Log.e("lwjing", "insertDB 非同意添加回复");
                return false;
            }
            //
            if (!ChatSysMessageBody.SysMessageType.ACTION_RESP_FED.equals(action)
                    && !ChatSysMessageBody.SysMessageType.ACTION_CREATE_GROUP.equals(action)
                    && !ChatSysMessageBody.SysMessageType.ACTION_RQST_FED.equals(action)
                    && !ChatSysMessageBody.SysMessageType.ACTION_DESTROY_GROUP.equals(action)) {

                Log.e("lwjing", "insertDB 非被添加或非被响应添加或非建群");
                return false;
            }

        }

        message.message = SendPlayload.matchContent(message);

        //开始执行存储的方法
        ContentValues values = new ContentValues();
        values.put(MQTTDatabaseHelper.TIMESTAMP, message.timestamp);
        values.put(MQTTDatabaseHelper.TYPE, message.type);
        values.put(MQTTDatabaseHelper.SENDER, message.sender);
        values.put(MQTTDatabaseHelper.RECEIVER, message.receiver);
        values.put(MQTTDatabaseHelper.STATUS, message.messageState);
        values.put(MQTTDatabaseHelper.READ, message.read);
        values.put(MQTTDatabaseHelper.MESSAGE, message.message);
        long rowId = sDatabase.replace(MQTTDatabaseHelper.MESSAGE_TABLE_NAME, null, values);
        return rowId != -1;
    }

    /**
     * 插入一整个集合的消息
     *
     * @param messageList
     * @return
     */
    public static boolean insert(List<ChatMessage> messageList) {
        String sql = "insert into " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME + " values (?,?,?,?,?,?,?);";
        Log.e(TAG, "xxx==sql:" + sql);
        SQLiteStatement statement = sDatabase.compileStatement(sql);
        sDatabase.beginTransaction();
        Iterator<ChatMessage> iterator = messageList.iterator();
        while (iterator.hasNext()) {
            ChatMessage message = iterator.next();
            statement.bindLong(1, message.timestamp);//插入时间
            statement.bindLong(2, message.type);
            statement.bindString(3, message.sender);
            statement.bindString(4, message.receiver);
            statement.bindLong(5, message.messageState);
            statement.bindLong(6, message.read);
            statement.bindString(7, message.message);
            statement.executeInsert();
        }
        sDatabase.setTransactionSuccessful();
        sDatabase.endTransaction();
        return true;
    }

    /**
     * 根据发送人id或接收人id删除与之相关的所有信息
     */
    public static void deleteFriendMsg(String id1, String id2) {
        int rows = sDatabase.delete(MQTTDatabaseHelper.MESSAGE_TABLE_NAME, "(sender=? and receiver=?) or (sender=? and receiver=?)", new
                String[]{id1, id2, id2, id1});
        Log.e(TAG, "xxx==删除了数据总数:" + rows);
    }

    /**
     * 删除所有群消息
     */
    public static void deleteGroupMsg(String id) {
        sDatabase.delete(MQTTDatabaseHelper.MESSAGE_TABLE_NAME, "receiver=? or sender=?", new String[]{id,id});
    }

    /**
     * 根据时间戳来删除某一条消息
     *
     * @param time
     */
    public static void delete(long time) {
        int rows = sDatabase.delete(MQTTDatabaseHelper.MESSAGE_TABLE_NAME, "timestamp=?", new String[]{"" + time});
        Log.e(TAG, "xxx==删除了数据总数:" + rows);
    }

    /**
     * 聊天界面中删除一条聊天记录
     *
     * @param sender
     * @param time
     */
    public static void delete(String sender, long time) {
        int rows = sDatabase.delete(MQTTDatabaseHelper.MESSAGE_TABLE_NAME, "timestamp=? and sender=?", new String[]{"" + time, sender});
        Log.e("lwjing", "xxx==删除了数据总数:" + rows);
    }

    /**
     * 更新接收到的消息的已读状态,0表示未读，1表示已读
     * 此处要注意，自己发送的消息的读状态默认是0,表示已读
     * 此方法只会更新一条记录，因为时间戳是唯一的
     */
    public static void updateReadingState(long time, String sender, int read) {
        String sql = "update " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME
                + " set " + MQTTDatabaseHelper.READ + "=? " +
                " where " + MQTTDatabaseHelper.TIMESTAMP + "=? and " +
                MQTTDatabaseHelper.SENDER + "=? ;";
        Log.e(TAG, "xxx==sql:" + sql);
        sDatabase.execSQL(sql, new String[]{String.valueOf(read), String.valueOf(time), sender});
    }

    /**
     * 此方法会更新所有的指定发送者发送过来的消息为已读状态
     *
     * @param sender
     */
    public static void updateReadingState(String sender) {
        String sql = "update " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME
                + " set " + MQTTDatabaseHelper.READ + "=1 " +
                " where " + MQTTDatabaseHelper.SENDER + "=? ;";
        sDatabase.execSQL(sql, new String[]{sender});
    }

    public static void updateReadingStateFromGroup(String groupId) {
        String sql = "update " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME
                + " set " + MQTTDatabaseHelper.READ + "=1" +
                " where " + MQTTDatabaseHelper.RECEIVER + "=? ;";
        sDatabase.execSQL(sql, new String[]{groupId});
    }

    /**
     * 更新自己发送的消息的状态
     */
    public static void updateSendingStatus(long time, String sender, int status) {
        String sql = "update " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME
                + " set " + MQTTDatabaseHelper.STATUS + "=? " +
                " where " + MQTTDatabaseHelper.TIMESTAMP + "=? and " +
                MQTTDatabaseHelper.SENDER + "=? ;";
        sDatabase.execSQL(sql, new String[]{String.valueOf(status), String.valueOf(time), sender});
    }

    /**
     * 根据指定的时间戳查询某一条消息,如果没有查到则为null
     */
    public static ChatMessage queryByTimestamp(long time) {
        //要查询的字段,这里查询所有字段
        String[] culumns = new String[]{
                MQTTDatabaseHelper.TIMESTAMP,
                MQTTDatabaseHelper.TYPE,
                MQTTDatabaseHelper.SENDER,
                MQTTDatabaseHelper.RECEIVER,
                MQTTDatabaseHelper.STATUS,
                MQTTDatabaseHelper.READ,
                MQTTDatabaseHelper.MESSAGE
        };
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME, culumns,
                "timestamp=?", new String[]{String.valueOf(time)}, null, null, null);
        ChatMessage message = null;
        if (cursor.moveToFirst()) {
            message = createMessage(cursor);
            cursor.close();
        }
        return message;
    }

    /**
     * 查询两个联系人之间的最新的一条联系记录
     * 用于显示在消息记录列表项中
     *
     * @return
     */
    public static ChatMessage queryRecentMessage(String id1, String id2) {
        //要查询的字段,这里查询所有字段
        String sql = "select * from " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME +
                " where " + "( " + MQTTDatabaseHelper.SENDER + "=? and " +
                MQTTDatabaseHelper.RECEIVER + "=? ) or (" +
                MQTTDatabaseHelper.SENDER + "=? and " + MQTTDatabaseHelper.RECEIVER + "=?)" +
                " order by " + MQTTDatabaseHelper.TIMESTAMP + " desc " + "limit 1;";
        Cursor cursor = sDatabase.rawQuery(sql, new String[]{id1, id2, id2, id1});
        ChatMessage message = null;
        if (cursor.moveToFirst()) {
            message = createMessage(cursor);
            cursor.close();
        }
        return message;
    }

    /**
     * 查询群聊天的最新的一条消息，由于群消息的接收人是群id,
     * @return
     */
    public static ChatMessage queryRecentMessageFromGroup(String groupId) {
        String sql = "select * from " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME +
                " where " + "( " + MQTTDatabaseHelper.SENDER + "=? or " +
                MQTTDatabaseHelper.RECEIVER + "=? )" +
                " order by " + MQTTDatabaseHelper.TIMESTAMP + " desc " + "limit 1;";
//        String sql = "select * from " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME +
//                " where " + MQTTDatabaseHelper.RECEIVER + " =?" +
//                " order by " + MQTTDatabaseHelper.TIMESTAMP + " desc " + "limit 1;";
        Cursor cursor = sDatabase.rawQuery(sql, new String[]{groupId,groupId});
        ChatMessage message = null;
        if (cursor.moveToFirst()) {
            message = createMessage(cursor);
            cursor.close();
        }
        return message;
    }

    /**
     * 根据群id查询所有的群消息记录
     *
     * @param groupId
     * @return
     */
    public static List<ChatMessage> queryByGroupId(String groupId) {
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME,
                null,
                MQTTDatabaseHelper.RECEIVER + "=?" + " or " + MQTTDatabaseHelper.SENDER + "=?",
                new String[]{groupId, groupId},
                null,
                null,
                MQTTDatabaseHelper.TIMESTAMP + " DESC");//ASC
        List<ChatMessage> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            ChatMessage message = createMessage(cursor);
            list.add(message);
        }
        cursor.close();
        return list;
    }

    /**
     * 查询与某个联系人相关的聊天消息，要么发送人是自己，要么接收人是自己
     * 这是为了与查群消息分开
     */
    public static List<ChatMessage> queryByClientId(String clientId_1, String clientId_2) {
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME,
                null,
                "(" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)" +
                        " or " +
                        "(" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)",
                new String[]{clientId_1, clientId_2, clientId_2, clientId_1},
                null,
                null,
                MQTTDatabaseHelper.TIMESTAMP + " DESC");//按时间降序排列
        List<ChatMessage> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            ChatMessage message = createMessage(cursor);
            list.add(message);
        }
        cursor.close();
        return list;
    }

    public static List<ChatMessage> queryByClientIdFilterReqF(String clientId_1, String clientId_2) {
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME,
                null,
                "(" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)" +
                        " or " +
                        "(" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)",
                new String[]{clientId_1, clientId_2, clientId_2, clientId_1},
                null,
                null,
                MQTTDatabaseHelper.TIMESTAMP + " DESC");//按时间降序排列
        List<ChatMessage> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            ChatMessage message = createMessage(cursor);
            ChatMessageBody messageBody = message.getMessageBody();
            if (message.getMessageBody() instanceof ChatSysMessageBody) {
                ChatSysMessageBody chatSysMessageBody = (ChatSysMessageBody) messageBody;
                if (ChatSysMessageBody.SysMessageType.ACTION_RQST_FED.equals(chatSysMessageBody.action)) {
                    continue;
                }
            }
            list.add(message);
        }
        cursor.close();
        return list;
    }


    /**
     * @param clientId_1
     * @param clientId_2
     * @return 查询与某个联系人相关的系统消息
     */
    public static List<ChatMessage> querySysByClientId(String clientId_1, String clientId_2) {
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME,
                null,
                "((" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)" +
                        " or " +
                        "(" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?))" +
                        " and " +
                        MQTTDatabaseHelper.TYPE + "=?",
                new String[]{clientId_1, clientId_2, clientId_2, clientId_1, ChatMessageBody.Type.SYSTEM + ""},
                null,
                null,
                null);
        List<ChatMessage> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            ChatMessage message = createMessage(cursor);
            list.add(message);
        }
        cursor.close();
        return list;
    }

    /**
     * 查询群消息总的消息未读数
     *
     * @param groupId
     * @return
     */
    public static int queryUnreadCountFromGroup(String groupId) {
        String sql = "select * from " + MQTTDatabaseHelper.MESSAGE_TABLE_NAME +
                " where " + MQTTDatabaseHelper.RECEIVER + " =? and " +
                MQTTDatabaseHelper.READ + "=0;";
        Cursor cursor = sDatabase.rawQuery(sql, new String[]{groupId});
        int count = 0;
        while (cursor.moveToNext()) {
            count++;
        }
        return count;
    }

    /**
     * 根据客户端id查询所有未读消息数
     *
     * @param clientId_1
     * @param clientId_2
     * @return
     */
    public static int queryUnReadByClientId(String clientId_1, String clientId_2) {
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME,
                new String[]{MQTTDatabaseHelper.TIMESTAMP},
                "((" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)" +
                        " or " +
                        "(" + MQTTDatabaseHelper.RECEIVER + "=? and " + MQTTDatabaseHelper.SENDER + "=?)) and " +
                        MQTTDatabaseHelper.READ + "=0",
                new String[]{clientId_1, clientId_2, clientId_2, clientId_1},
                null,
                null,
                null);
        int count = 0;
        while (cursor.moveToNext()) {
            count++;
        }
        cursor.close();
        return count;
    }

    /**
     * 查询总的未读消息数量，用于在主界面的消息tab上显示
     * 未读表示read字段为0
     *
     * @return
     */
    public static int queryTotalUnreadCount() {
        int count = 0;
        //只查一列方便提高查找速度，减少查找时间
        Cursor cursor = sDatabase.query(MQTTDatabaseHelper.MESSAGE_TABLE_NAME,
                new String[]{MQTTDatabaseHelper.TIMESTAMP},
                MQTTDatabaseHelper.READ + "=0",
                null,
                null,
                null,
                null);
        while (cursor.moveToNext()) {
            count++;
        }
        cursor.close();
        return count;
    }

    /**
     * 从数据库中查到的记录创建java对象
     *
     * @param cursor
     * @return
     */
    private static ChatMessage createMessage(Cursor cursor) {
        //        final int type =
        //        HxMessage message = Playload.createMessageWithType(type);//type已经赋值
        ChatMessage message = new ChatMessage();
        message.type = cursor.getInt(cursor.getColumnIndex(MQTTDatabaseHelper.TYPE));
        message.timestamp = cursor.getLong(cursor.getColumnIndex(MQTTDatabaseHelper.TIMESTAMP));
        message.sender = cursor.getString(cursor.getColumnIndex(MQTTDatabaseHelper.SENDER));
        message.receiver = cursor.getString(cursor.getColumnIndex(MQTTDatabaseHelper.RECEIVER));
        message.messageState = cursor.getInt(cursor.getColumnIndex(MQTTDatabaseHelper.STATUS));
        message.read = cursor.getInt(cursor.getColumnIndex(MQTTDatabaseHelper.READ));
        message.message = cursor.getString(cursor.getColumnIndex(MQTTDatabaseHelper.MESSAGE));
        message.messageBody = ReceivePlayload.getMessageBodyByType(message.type, message.message);
        return message;
    }

    /**
     * 程序退出的时候关闭数据库
     */
    public static void close() {
        sDatabase.close();
    }

    /**
     * 可以实现数据库建表的帮助类
     */
    private static class MQTTDatabaseHelper extends SQLiteOpenHelper {
        //数据库名
        private static final String DATABASE_NAME      = "hxMessage.db";
        //表名
        private static final String MESSAGE_TABLE_NAME = "LocalMessageTable";
        //版本名称
        private static final int    DATABASE_VERSION   = 1;
        /*****下面是数据库表中字段表示的意思******/
        //消息的时间戳，唯一
        private static final String TIMESTAMP          = "timestamp";
        //消息类型
        private static final String TYPE               = "type";
        //发送人id
        private static final String SENDER             = "sender";
        //接收人id
        private static final String RECEIVER           = "receiver";
        //消息的状态
        private static final String STATUS             = "status";
        //消息是否已读,0表示未读，1表示已读
        private static final String READ               = "read";
        //消息的内容
        private static final String MESSAGE            = "message";

        MQTTDatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        /**
         * 创建表
         */
        @Override
        public void onCreate(SQLiteDatabase database) {
            String createArrivedTableStatement = "CREATE TABLE "
                    + MESSAGE_TABLE_NAME + "("
                    + TIMESTAMP + " INTEGER PRIMARY KEY, "//时间
                    + TYPE + " INTEGER, "//类型
                    + SENDER + " TEXT, "//发送人
                    + RECEIVER + " TEXT, "//接收人
                    + STATUS + " INTEGER, "//发送状态
                    + READ + " INTEGER, "//是否已读
                    + MESSAGE + " TEXT " + ");";//发送内容
            try {
                database.execSQL(createArrivedTableStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            try {
                db.execSQL("DROP TABLE IF EXISTS " + MESSAGE_TABLE_NAME);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            onCreate(db);
        }
    }
}
